﻿using System.Collections.Generic;
using System.Linq;

namespace CRMS.Models
{
    public class CourseRep
    {
        private StoreDBDataContext storeDB = new StoreDBDataContext();
        //
        //Query Methods
        /// <summary>
        /// 获取所有的课程信息
        /// </summary>
        /// <returns>返回课程集合</returns>
        public IQueryable<Course> GetCourses()
        {
            return from c in storeDB.Courses select c;
        }
        /// <summary>
        /// 通过院系的ID，获取该院系的所有课程
        /// </summary>
        /// <param name="did">给定院系ID参数</param>
        /// <returns>返回给定院系所有课程集合</returns>
        public IQueryable<Course> GetCoursesByDID(string did)
        {
            return storeDB.Courses.Where(c => c.DepartmentId == did);
        }
        /// <summary>
        /// 通过院系的名称，获取该院系的所有课程
        /// </summary>
        /// <param name="name">院系名称</param>
        /// <returns>所有课程集合</returns>
        public IQueryable<Course> GetCoursesByDName(string dName)
        {
            return storeDB.Courses.Where(c => c.Department.DepartmentName == dName);
        }
        /// <summary>
        /// 通过课程名获取课程对象
        /// </summary>
        /// <param name="name">给定课程名参数</param>
        /// <returns>返回给定课程名的实例</returns>
        public IQueryable<Course> GetCourseByName(string name)
        {
            return storeDB.Courses.Where(c => c.CourseName == name);
        }
        /// <summary>
        /// 通过课程ID获取该课程的详细信息
        /// </summary>
        /// <param name="id">课程ID参数</param>
        /// <returns>返回给定课程ID对象</returns>
        public Course GetCourseByID(string id)
        {
            return storeDB.Courses.SingleOrDefault(c => c.CourseId == id);
        }
        /// <summary>
        /// 根据课程的名称，在课程表中查出课程编号。
        /// 再根据查出来的课程编号，在教室开课表中查出教师标号
        /// 最后，根据查出来的教师编号再在教室表中查找教师信息
        /// </summary>
        /// <param name="name">课程名称参数</param>
        /// <returns>返回开设开课程的所有教师</returns>
        public IQueryable<Teacher> GetTeachersByCourseName(string cName)
        {
            return storeDB.Teachers.Where(t => t.TeacherId == 
                   (storeDB.TeacherCourses.Where(tc => tc.CourseId == 
                   storeDB.Courses.Where(c => c.CourseName == cName).Select(c => c.CourseId).First()
                   ).Select(tc => tc.TeacherId).First()
                   )
                   );
        }
        //
        //Insert/Delete
        /// <summary>
        /// 向数据库添加一条课程记录
        /// </summary>
        /// <param name="course">课程对象参数</param>
        public void Add(Course course)
        {
            storeDB.Courses.InsertOnSubmit(course);
        }
        /// <summary>
        /// 从数据库课程表删除一条记录
        /// </summary>
        /// <param name="course">课程对象参数</param>
        public void Delete(Course course)
        {
            storeDB.Courses.DeleteOnSubmit(course);
        }

        //
        //Persistence
        /// <summary>
        /// 把更改保存到数据库
        /// </summary>
        public void Save()
        {
            storeDB.SubmitChanges();
        }
    }
}
